package com.bw.Dao;

import com.bw.bean.Sugar;
import com.bw.bean.Sugar1;
import com.bw.bean.Sugar3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("select hour(create_time) `name`,count(*) `value` from order_info GROUP BY hour(create_time)")
    List<Sugar> getList1();

    @Select("select tp.paper_title `name`,\n" +
            "count(user_id) `num`,\n" +
            "sum(te.score)/count(user_id) avgScore,\n" +
            "sum(te.duration_sec)/count(user_id) avgTime\n" +
            "from test_exam te \n" +
            "LEFT JOIN test_paper tp ON tp.id=te.paper_id\n" +
            "GROUP BY tp.paper_title")
    List<Sugar1> getList2();

    @Select("select count(*) `value` from order_info where date(create_time)='2021-04-23'")
    double getList3();

    @Select("select ci.course_name `name`,\n" +
            "avg(ri.review_stars) `avgScore`,\n" +
            "count(ri.user_id) `num1`,\n" +
            "count(ri.review_stars=5) `num2`,\n" +
            "count(ri.review_stars=5)/count(ri.user_id) `lv`\n" +
            "from review_info ri \n" +
            "LEFT JOIN course_info ci ON ci.id=ri.course_id\n" +
            "GROUP BY ci.course_name")
    List<Sugar3> getList4();
}
